ALTER PROCEDURE [dbo].[spe_grab_ucis_wjcjbz2]
AS

/****************************************************
  Program_Name: spe_grab_ucis_wjcjbz
  Program_Desc: UCIS月初催缴-主任务由Job调用
  Commentary:上海过河兵
  Revision: V1.0
  Author: Tao
  Date: 2019-01-04
***************************************************/

Begin
  Declare @g_nf int
  Declare @g_yf int
  Declare @g_id int
  Declare @g_tqrq1 datetime
  Declare @g_tqrq2 datetime
  Declare @g_yhbh  varchar(20)
  Declare @g_yhbm  varchar(4)
  Declare @g_yhzh varchar(50)
  Declare @g_lxdh varchar(50)
  Declare @g_ysdz varchar(100)
  Declare @g_sjhm varchar(30)  
  Declare @g_tqje  decimal(13,2)
  Declare @g_tqsf decimal(13,2)
  Declare @g_tqpsf decimal(13,2)
  Declare @g_tqljf decimal(13,2)
  Declare @g_sfznj decimal(13,2)
  Declare @g_tqlx  varchar(6)
  Declare @g_lxdh2 varchar(50)
  Declare @g_itemid bigint
  Declare @g_deptcode varchar(10)
  Declare @g_psfznj decimal(13,2)
  Declare @g_ljfznj decimal(13,2)
  Declare @plancount int
  Declare @datacount int
  Declare @totalcount int
  Declare @phonecount int
  Declare @DEPTCODE varchar(10)
  Declare @rec_guid varchar(36)
  Declare @smsmix varchar(2)
	DECLARE @SENDYEARMONTH INT
	DECLARE @BATCH int
	DECLARE @MESSAGETYPECODE VARCHAR(50)
	declare @MESSAGETYPENAME VARCHAR(50)
	declare @MESSAGECOUNT VARCHAR(50)
	declare @MESSAGEINFOID VARCHAR(50)
	declare @MESSAGESTATUS INT
	declare @CREATETIME DATETIME
	declare @UCIS_PLAN_COUNT INT
	DECLARE @tguid VARCHAR(36)
	DECLARE @sname VARCHAR(255)
declare @te_Starttime DATETIME

Set @te_Starttime = GetDate()

	--检查UCIS是否有待处理任务
	SELECT @UCIS_PLAN_COUNT = Count(*) From OPENQUERY(UCIS115,'SELECT * FROM ucis.MessageInfo')
		WHERE MESSAGETYPECODE = 'A00011' AND MESSAGESTATUS = '1' AND SENDYEARMONTH = REPLACE(CONVERT(varchar(7),GETDATE(),120) , '-', '') and CREATETIME> CONVERT(varchar(100), GETDATE(), 23) 
	PRINT @UCIS_PLAN_COUNT
  /*--检查是否有待处理任务
  Select @Plancount = Count(*)
    From Ucis_Smssendplan
   Where Type_Id = 'A00011'
     And Getdate() >= Jhkstime
     And Getdate() <= Jhjstime
     And Status = 0
	*/
	--如果Plancount大于0则说明ucis中存在计划任务
  If @UCIS_PLAN_COUNT>0
  Begin
		SELECT top 1 @MESSAGEINFOID=MESSAGEINFOID,
								 @SENDYEARMONTH=SENDYEARMONTH,--年月
								 @BATCH=BATCH,--批次
								 @MESSAGETYPENAME=MESSAGETYPENAME,--类型（名称）
								 @DEPTCODE=DEPTCODE,--部门编号
								 @MESSAGECOUNT=MESSAGECOUNT,--短信总数
								 @MESSAGESTATUS=MESSAGESTATUS,--短信状态
								 @CREATETIME=CREATETIME,--时间
								 @MESSAGETYPECODE=MESSAGETYPECODE --类型(编号)
		From OPENQUERY(UCIS115,'SELECT * FROM ucis.MessageInfo')
		WHERE MESSAGETYPECODE = 'A00011' 
			AND MESSAGESTATUS = '1' 
			AND SENDYEARMONTH = REPLACE(CONVERT(varchar(7),GETDATE(),120) , '-', '') 
			ORDER BY CREATETIME DESC

--查询本地计划表UCIS_SMSSENDPLAN是否有匹配的计划任务
	  SELECT @plancount=COUNT(*) FROM Ucis_Smssendplan 
		 WHERE TYPE_ID = @MESSAGETYPECODE 
			 AND BATCH=@BATCH 
			 AND DEPTCODE=@DEPTCODE
			 AND REPLACE(CONVERT(varchar(7),CTEATEDDATE,120) , '-', '')=@SENDYEARMONTH
			 AND MESSAGEINFOID=@MESSAGEINFOID
		-- PRINT @Plancount
		-- PRINT '本地是否有计划↑'
		 if @plancount<1
			--创建计划任务
			BEGIN
					INSERT INTO Ucis_Smssendplan(rec_guid,TYPE_ID,BATCH,CTEATEDDATE,DEPTCODE,SMS_NAME,status,JHKSTIME,MESSAGEINFOID,SMS_JSSC,MESSAGECOUNT)
					SELECT top 1 NEWID(),MESSAGETYPECODE,BATCH,GETDATE(),m1.DEPTCODE,
					LEFT(SENDYEARMONTH,4)+'年'+SUBSTRING(SENDYEARMONTH,5,6)+'月'+c1.Department+m1.MESSAGETYPENAME + '批次('+CAST(BATCH as VARCHAR)+'-'+CAST(BATCHTIMES as VARCHAR) +')',0,CREATETIME,MESSAGEINFOID,300,MESSAGECOUNT
					From OPENQUERY(UCIS115,'SELECT * FROM ucis.MessageInfo')m1
					LEFT JOIN ucis_deptcode c1 on c1.DEPTCODE=m1.DEPTCODE
					WHERE MESSAGETYPECODE = 'A00011' AND MESSAGESTATUS = '1' AND SENDYEARMONTH = REPLACE(CONVERT(varchar(7),GETDATE(),120) , '-', '') AND MESSAGEINFOID=@MESSAGEINFOID
					ORDER BY CREATETIME DESC
				
					Declare plan_A00011 cursor for select top 1 REC_GUID,DEPTCODE from ucis_smssendplan 
							where type_id='A00011' and STATUS=0 AND MESSAGEINFOID = @MESSAGEINFOID ORDER BY CTEATEDDATE DESC
																					 
					Open plan_A00011 fetch next from plan_A00011 into @rec_guid,@DEPTCODE
					While @@fetch_status=0
					Begin
						--检查UCIS中是否存在需要处理的任务数据
						
						Select @Datacount = Count(*)
							FROM OPENQUERY(UCIS115,'SELECT * FROM ucis.Ucis_Wj_Cjbz2 where ghbtype is null ') 
							 where  Yf = Month(Getdate())
							 And Nf = Year(Getdate())
							 And Deptcode = @Deptcode

						PRINT @Datacount	
 
						If @Datacount>0
						Begin
							Begin Try
							Insert Into Ucis_Sms_Monitoring
								(Sms_Guid, Source_Sum, Type_Id, Grab_Starttime, Createdate)
							Values
								(@Rec_Guid, @Datacount, 'A00011', Getdate(), Getdate())

							Insert Into Ucis_Cj_Result
								(Cj_Guid, Type, Sourcedata, Createdate)
							Values
								(@Rec_Guid, 'A00011', @Datacount, Getdate())
								
							Begin
								--调整了dblink查询语句sql方式，提高查询性能跟效率 Yf = Month(Getdate()) And Nf = Year(Getdate())
								Insert Into Ucis_Wj_Cjbz2
								 (Id,Nf,Yf,Yhbh,Tqje,Yhbm,Yhzh,Lxdh,Ysdz,Sjhm,Tqlx,Tqrq1,Tqrq2,Tqsf,Tqpsf,Tqljf,Lxdh2,
									Sfznj,Psfznj,Ljfznj,Itemid,Deptcode,Sms_Guid,Smsmix,ReducedMoney,isgsjt)
								 Select Id,Nf,Yf,Yhbh,Tqje + Sfznj + Psfznj + Ljfznj,Yhbm,Yhzh,REPLACE(Lxdh, ' ', ''),
												Ysdz,Sjhm,Tqlx,Tqrq1,Tqrq2,Tqsf,Tqpsf,Tqljf,REPLACE(Lxdh2, ' ', ''),
												Sfznj,Psfznj,Ljfznj,Itemid,Deptcode,@Rec_Guid,Smsmix,case when ReducedMoney is null then 0 else ReducedMoney end,0
									 From OPENQUERY(UCIS115,'SELECT * FROM ucis.Ucis_Wj_Cjbz2  Where Ghbtype Is Null and yf=to_char(sysdate,''mm'') and nf=to_char(sysdate,''yyyy'')')
									Where 1=1
										And Deptcode = @Deptcode
																				
								--更改状态
								/*Update OPENQUERY 
									(UCIS115,'SELECT * from ucis.Ucis_Wj_Cjbz2')
									 Set Ghbtype = 1
								 Where Id In (Select ID From Ucis_Wj_Cjbz2 Where Sms_Guid = @Rec_Guid)
									 And Deptcode = @Deptcode*/
								--调整更改状态
								--调整了dblink查询语句sql方式，提高查询性能跟效率 Yf = Month(Getdate()) And Nf = Year(Getdate())
								Update OPENQUERY(UCIS115,'SELECT * FROM ucis.Ucis_Wj_Cjbz2 where ghbtype is null and yf=to_char(sysdate,''mm'') and nf=to_char(sysdate,''yyyy'')')
									 Set Ghbtype = 1
								Where 1=1
							 And Deptcode = @Deptcode
							
							End
							
							--计数
							Begin
								Select @Phonecount = Count(*)
									From Ucis_Wj_Cjbz2
								 Where Sms_Guid = @Rec_Guid
									 And Len(Sjhm) = 11
									 
								Update Ucis_Cj_Result
									 Set Phone_Count = @Phonecount
								 Where Cj_Guid = @Rec_Guid
								 
								Select @Totalcount = Count(*)
									From Ucis_Wj_Cjbz2
								 Where Sms_Guid = @Rec_Guid
								 
								Update Ucis_Sms_Monitoring
									 Set Grab_Sum     = @Totalcount,
											 Grab_Endtime = Getdate(),
											 Grab_Status  = 1
								 Where Sms_Guid = @Rec_Guid
								 
								Insert Into Ucis_Sms_Grab ([sms_guid],[type_id]) Values (@Rec_Guid, 'A00011')
								
								/*Update Ucis_Smssendplan
									 Set Status = 1
								 Where Rec_Guid = @Rec_Guid
									 And Status = 0*/
							End
						End Try
						Begin Catch
							--如果异常将事务内容回滚
							RollBack Tran
							Exec Dbo.Proc_Error_Record 'Spe_Grab_UCIS_Wjcjbz2月初催缴抓取数据失败!'
							Print 'Spe_grab_ucis_wjcjbz Fail'
						End Catch    
						End  
						Fetch Next From Plan_a00011 Into @rec_guid, @deptcode
					End
				Close Plan_a00011
			Deallocate Plan_a00011
		END
			--if @MESSAGESTATUS=1
				BEGIN 
					UPDATE TOP(1) OPENQUERY(UCIS115,'SELECT * FROM ucis.MessageInfo') SET MESSAGESTATUS = '2',modifytime = GETDATE()
					WHERE SENDYEARMONTH=@SENDYEARMONTH AND
								BATCH=@BATCH AND
								MESSAGETYPENAME=@MESSAGETYPENAME AND
								DEPTCODE=@DEPTCODE AND
								MESSAGECOUNT=@MESSAGECOUNT AND
								MESSAGESTATUS=@MESSAGESTATUS AND
								CREATETIME=@CREATETIME AND
								MESSAGETYPECODE=@MESSAGETYPECODE AND
								MESSAGEINFOID=@MESSAGEINFOID
				END
				BEGIN
					select top 1 @tguid = REC_GUID,@sname = sms_name from ucis_smssendplan 
							where type_id='A00011' AND MESSAGEINFOID = @MESSAGEINFOID ORDER BY CTEATEDDATE DESC
					INSERT INTO message_operation_his VALUES (@tguid,@sname,'job', GETDATE(), 'UCIS存在计划，进行计划抓取',NEWID());
				END
				BEGIN
					Insert Into Log_Proc_Dealtime
						(Log_Guid, Proc_Name, Run_No, Starttime, Endtime, Dealinfo, Dealtime, Remark)
					Values
						(Newid(), 'spe_grab_ucis_wjcjbz2', @tguid, @te_Starttime, Getdate(), '月初催缴计划抓取及短信内容抓取', DateDiff(MS, @te_Starttime, GetDate()), 'spe_grab_ucis_wjcjbz2任务调用:水务催缴流程月初催缴')

				END
  End
End